package com.coolw.jdbc;

import com.coolw.jdbc.util.DbPoolUtil;
import org.junit.Test;

import java.sql.*;

/**
 * jdbc 连接池测试类
 *
 * @author coolw
 * @date 2023/12/9 15:43
 */
public class JdbcPoolTest {

    /**
     * 批量新增(使用Statement对象)
     */
    @Test
    public void testBatchAdd1() {
        String sql1 = "insert into t_user (user_name, password) values ('15000000001', '123456')";
        String sql2 = "insert into t_user (user_name, password) values ('15000000002', '123456')";
        String sql3 = "insert into t_user (user_name, password) values ('15000000003', '123456')";

        Connection conn = null;
        Statement stat = null;
        try {
            conn = DbPoolUtil.getConnection();
            stat = conn.createStatement();
            stat.addBatch(sql1);
            stat.addBatch(sql2);
            stat.addBatch(sql3);

            // 执行批量操作
            stat.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbPoolUtil.close(conn, stat, null);
        }
    }

    /**
     * 批量新增(使用Statement对象)
     */
    @Test
    public void testBatchAdd2() {
        String sql = "insert into t_user (user_name, password) values (?, ?)";

        Connection conn = null;
        PreparedStatement stat = null;
        try {
            conn = DbPoolUtil.getConnection();
            stat = conn.prepareStatement(sql);

            stat.setString(1, "15000000004");
            stat.setString(2, "123456");
            // 添加到批量处理中
            stat.addBatch();

            stat.setString(1, "15000000005");
            stat.setString(2, "123456");
            // 添加到批量处理中
            stat.addBatch();

            stat.setString(1, "15000000006");
            stat.setString(2, "123456");
            // 添加到批量处理中
            stat.addBatch();

            // 执行批量操作
            stat.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbPoolUtil.close(conn, stat, null);
        }
    }

    /**
     * 获取主键
     */
    @Test
    public void testGetPrimaryKey() {
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;

        try {
            conn = DbPoolUtil.getConnection();
            stat = conn.createStatement();

            String sql = "insert into t_user (user_name, password) values ('15000000007', '123456')";

            // 执行SQL并且指定返回自增的主键值
            stat.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

            // 获取返回的主键值
            rs = stat.getGeneratedKeys();
            while (rs.next()) {
                // 获取第一列的值(id)，从左往右，下标从1开始
                int id = rs.getInt(1);
                System.out.println("自增主键=" + id);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbPoolUtil.close(conn, stat, rs);
        }
    }

    /**
     * 获取数据库的元数据
     */
    @Test
    public void testMetadata() {
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;

        try {
            conn = DbPoolUtil.getConnection();
            stat = conn.createStatement();

            // 数据库元数据信息
            DatabaseMetaData metaData = conn.getMetaData();
            System.out.println("驱动版本:" + metaData.getDriverMajorVersion());
            System.out.println("连接地址:" + metaData.getURL());
            System.out.println("用户名:" + metaData.getUserName());
            System.out.println("数据库名称:" + metaData.getDatabaseProductName());

            // 获取t_user表的信息
            rs = stat.executeQuery("select * from t_user");
            ResultSetMetaData userMetaData = rs.getMetaData();
            // 获取t_user表的字段数量
            int userColumnCount = userMetaData.getColumnCount();
            for (int i = 1; i <= userColumnCount; i++) {
                String columnName = userMetaData.getColumnName(i);
                String columnTypeName = userMetaData.getColumnTypeName(i);
                System.out.println("columnName=" + columnName + ",columnTypeName=" + columnTypeName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbPoolUtil.close(conn, stat, rs);
        }
    }
}
